﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using seo.Data;

namespace seo.DAO
{
    class YiLookDao
    {
        private MySqlConnection conn;

        public YiLookDao(MySqlConnection conn)
        {
            this.conn = conn;
        }

        public List<YiLookCatalog> GetAllCatalog()
        {
            List<YiLookCatalog> list = new List<YiLookCatalog>();
            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;
            command.CommandText = "select id, name, eng_name, url, page_count from catalog";
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                YiLookCatalog catalog = new YiLookCatalog();
                catalog.id = reader.GetInt32(0);
                catalog.name = reader.GetString(1);
                catalog.eng_name = reader.GetString(2);
                catalog.url = reader.GetString(3);
                catalog.page_count = reader.GetInt32(4);
                list.Add(catalog);
            }
            reader.Close();
            return list;
        }

        public List<YiLookArt> GetAllArts()
        {
            List<YiLookArt> list = new List<YiLookArt>();
            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;
            command.CommandText = "SELECT id,cat_id,name,eng_name,url,finish_flg,publish_flg FROM art where publish_flg = 0 order by id asc";
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                YiLookArt art = new YiLookArt();
                art.id = reader.GetInt32(0);
                art.cat_id = reader.GetInt32(1);
                art.name = reader.GetString(2);
                if (reader.GetValue(3) != DBNull.Value)
                {
                    art.eng_name = reader.GetString(3);
                }
                art.url = reader.GetString(4);
                art.finish_flg = reader.GetString(5);
                art.publish_flg = reader.GetString(6);
                list.Add(art);
            }
            reader.Close();
            return list;
        }

        public String GetContentByChapter(YiLookChapter chapter)
        {
            String content = "";
            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;
            command.CommandText = "SELECT artid,chapterid,content FROM content where artid = ?artid and chapterid = ?chapterid";
            command.Parameters.AddWithValue("?artid", chapter.artid);
            command.Parameters.AddWithValue("?chapterid", chapter.chapterid);
            reader = command.ExecuteReader();
            if (reader.Read())
            {
                content = reader.GetString(2);
            }
            reader.Close();
            return content;
        }

        public List<YiLookChapter> GetChaptersByArt(int artid)
        {
            List<YiLookChapter> list = new List<YiLookChapter>();
            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;
            command.CommandText = "select artid, chapterid, url, collect_date, upload_flg, upload_date from chapter where artid = '" + artid + "'";
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                YiLookChapter chapter = new YiLookChapter();
                chapter.artid = reader.GetInt32(0);
                chapter.chapterid = reader.GetInt32(1);
                chapter.url = reader.GetString(2);
                chapter.collect_date = reader.GetDateTime(3);
                chapter.upload_flg = reader.GetString(4);
                chapter.upload_date = reader.GetDateTime(5);
                list.Add(chapter);
            }
            reader.Close();
            return list;
        }

        public List<YiLookChapter> GetUnUploadChaptersByArt(int artid)
        {
            List<YiLookChapter> list = new List<YiLookChapter>();
            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;
            command.CommandText = "select artid, chapterid, url, collect_date, upload_flg, upload_date from chapter where upload_flg = '0' and artid = '" + artid + "'";
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                YiLookChapter chapter = new YiLookChapter();
                chapter.artid = reader.GetInt32(0);
                chapter.chapterid = reader.GetInt32(1);
                chapter.url = reader.GetString(2);
                chapter.collect_date = reader.GetDateTime(3);
                chapter.upload_flg = reader.GetString(4);
                chapter.upload_date = reader.GetDateTime(5);
                list.Add(chapter);
            }
            reader.Close();
            return list;
        }

        public int getMaxId()
        {
            int maxId = 0;

            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;

            command.CommandText = "select max(id)+1 as maxId from art";

            reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    maxId = reader.GetInt32(0);
                }
            }
            catch (Exception ex)
            {
                maxId = 0;
            }
            reader.Close();
            return maxId;
        }

        public bool IsArtExist(string url)
        {
            bool result = false;

            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;

            command.CommandText = "select id from art where url = '" + url +"'";
            reader = command.ExecuteReader();
            if (reader.Read()) result = true;
            reader.Close();
            return result;
        }

        public bool IsChapterExist(YiLookChapter chapter)
        {
            bool result = false;

            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;

            command.CommandText = "select * from chapter where url = '" + chapter.url + "'";
            reader = command.ExecuteReader();
            if (reader.Read()) result = true;
            reader.Close();
            return result;
        }

        public int InsertArt(YiLookArt art)
        {
            int result = 0;
            if (IsArtExist(art.url)) return result;
            string sql = "insert into art(id, cat_id, name, url, finish_flg, collect_date,publish_flg) values(?id, ?cat_id, ?name, ?url, ?finish_flg, ?collect_date,?publish_flg)";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("?id", getMaxId());
            cmd.Parameters.AddWithValue("?cat_id", art.cat_id);
          //  string name = UTF8.GetString(System.Text.Encoding.Convert("gb2312", "u", GB2312.GetBytes(str)));  
            cmd.Parameters.AddWithValue("?name", art.name);
            cmd.Parameters.AddWithValue("?url", art.url);
            cmd.Parameters.AddWithValue("?finish_flg", art.finish_flg);
            cmd.Parameters.AddWithValue("?collect_date", DateTime.Now);
            cmd.Parameters.AddWithValue("publish_flg","0");
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {

            }
            return result;
        }

        public int updateCatalogPageCount(YiLookCatalog catalog)
        {
            int result = 0;
            string sql = "update catalog set page_count = ?page_count where id =?id";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("?id", catalog.id);
            cmd.Parameters.AddWithValue("?page_count", catalog.page_count);
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {

            }
            return result;
        }

        public int InsertArt(string url)
        {
            int result = 0;
            if (IsArtExist(url)) return result;
            string sql = "insert into art(id, url, collect_date) values(?id, ?url, ?collect_date)";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("?id", getMaxId());
            cmd.Parameters.AddWithValue("?url", url);
            cmd.Parameters.AddWithValue("?collect_date", DateTime.Now);
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {

            }
            return result;
        }

        public int InsertChapter(YiLookChapter chapter)
        {
            int result = 0;
            if (IsChapterExist(chapter)) return result;
            string sql = "insert into chapter(artid, chapterid, url, collect_date, upload_flg, upload_date) ";
            sql += "values(?artid, ?chapterid, ?url, ?collect_date, ?upload_flg, ?upload_date)";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("?artid", chapter.artid);
            cmd.Parameters.AddWithValue("?chapterid", chapter.chapterid);
            cmd.Parameters.AddWithValue("?url", chapter.url);
            cmd.Parameters.AddWithValue("?collect_date", DateTime.Now);
            cmd.Parameters.AddWithValue("?upload_flg", "0");
            cmd.Parameters.AddWithValue("?upload_date", DateTime.Now);
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                
            }
            return result;
        }

        public int InsertContent(YiLookChapter chapter, String content)
        {
            int result = 0;
            string sql = "insert into content(artid, chapterid, content, collect_date) ";
            sql += "values(?artid, ?chapterid, ?content, ?collect_date)";
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("?artid", chapter.artid);
            cmd.Parameters.AddWithValue("?chapterid", chapter.chapterid);
            cmd.Parameters.AddWithValue("?content", content);
            cmd.Parameters.AddWithValue("?collect_date", DateTime.Now);
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {

            }
            return result;
        }

        public int getMaxChapterId(int artid)
        {
            int maxId = 0;

            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;

            command.CommandText = "select max(chapterid) as maxId from chapter where artid = " + artid + "";
            reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    maxId = reader.GetInt32(0);
                }
            }
            catch (Exception ex)
            {
                maxId = 0;
            }
            reader.Close();
            return maxId;
        }
    }
}
